package cn.meye11.crm.mapper;

import cn.meye11.crm.common.DateUtils;
import cn.meye11.crm.common.RegexUtils;
import cn.meye11.crm.common.StringUtils;
import cn.meye11.crm.domain.entity.MemberEntity;
import cn.meye11.crm.domain.query.MemberQuery;
import org.apache.ibatis.jdbc.SQL;

/**
 * 会员信息表(Member)表数据库访问层 SQL组合类
 * 
 * @author makejava
 * @since 2023-11-11 13:34:00
 */

public class MemberProvider {

    public String list(MemberQuery query) {

        SQL sql = new SQL();
        sql.SELECT("*");
        sql.FROM(MemberEntity.Table + " mem ");
        sql.WHERE(wheres(query));
        sql.ORDER_BY(query.getSort() + " " + query.getDirection());
        return sql.toString();
    }

    public String count(MemberQuery query) {

        SQL sql = new SQL();
        sql.SELECT("count (id)");
        sql.FROM(MemberEntity.Table + " mem ");
        sql.WHERE(wheres(query));
        return sql.toString();
    }

    private String wheres(MemberQuery query) {
        StringBuilder where = new StringBuilder();
        if (query != null) {
            // 不为空时，必须为逗号分割的数字才能查询
            String id = StringUtils.commaDelimitedTrimEmptyString(query.getId());
            if (StringUtils.isNotBlank(id) && RegexUtils.check(RegexUtils.Type.ONLY_NUMBER_OR_EMPTY, id)) {
                where.append("mem.id in (" + id + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getName())) {
                where.append("mem.name = '" + query.getName() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getNameLike())) {
                where.append("mem.name like '%" + query.getNameLike() + "%'").append(" and ");
            }
            String nameIn = StringUtils.toSqlInString(query.getNameIn());
            if (StringUtils.isNotBlank(nameIn)) {
                where.append("mem.name in (" + nameIn + ")").append(" and ");
            }
            String nameNotIn = StringUtils.toSqlInString(query.getNameNotIn());
            if (StringUtils.isNotBlank(nameNotIn)) {
                where.append("mem.name not in (" + nameNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMobile())) {
                where.append("mem.mobile = '" + query.getMobile() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMobileLike())) {
                where.append("mem.mobile like '%" + query.getMobileLike() + "%'").append(" and ");
            }
            String mobileIn = StringUtils.toSqlInString(query.getMobileIn());
            if (StringUtils.isNotBlank(mobileIn)) {
                where.append("mem.mobile in (" + mobileIn + ")").append(" and ");
            }
            String mobileNotIn = StringUtils.toSqlInString(query.getMobileNotIn());
            if (StringUtils.isNotBlank(mobileNotIn)) {
                where.append("mem.mobile not in (" + mobileNotIn + ")").append(" and ");
            }
            if (query.getRechargeBalance() != null) {
                where.append("mem.recharge_balance = " + query.getRechargeBalance()).append(" and ");
            }
            if (query.getGiftBalance() != null) {
                where.append("mem.gift_balance = " + query.getGiftBalance()).append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMail())) {
                where.append("mem.mail = '" + query.getMail() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMailLike())) {
                where.append("mem.mail like '%" + query.getMailLike() + "%'").append(" and ");
            }
            String mailIn = StringUtils.toSqlInString(query.getMailIn());
            if (StringUtils.isNotBlank(mailIn)) {
                where.append("mem.mail in (" + mailIn + ")").append(" and ");
            }
            String mailNotIn = StringUtils.toSqlInString(query.getMailNotIn());
            if (StringUtils.isNotBlank(mailNotIn)) {
                where.append("mem.mail not in (" + mailNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getAddress())) {
                where.append("mem.address = '" + query.getAddress() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getAddressLike())) {
                where.append("mem.address like '%" + query.getAddressLike() + "%'").append(" and ");
            }
            String addressIn = StringUtils.toSqlInString(query.getAddressIn());
            if (StringUtils.isNotBlank(addressIn)) {
                where.append("mem.address in (" + addressIn + ")").append(" and ");
            }
            String addressNotIn = StringUtils.toSqlInString(query.getAddressNotIn());
            if (StringUtils.isNotBlank(addressNotIn)) {
                where.append("mem.address not in (" + addressNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getSex())) {
                where.append("mem.sex = '" + query.getSex() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getSexLike())) {
                where.append("mem.sex like '%" + query.getSexLike() + "%'").append(" and ");
            }
            String sexIn = StringUtils.toSqlInString(query.getSexIn());
            if (StringUtils.isNotBlank(sexIn)) {
                where.append("mem.sex in (" + sexIn + ")").append(" and ");
            }
            String sexNotIn = StringUtils.toSqlInString(query.getSexNotIn());
            if (StringUtils.isNotBlank(sexNotIn)) {
                where.append("mem.sex not in (" + sexNotIn + ")").append(" and ");
            }
            if (query.getBirthday() != null) {
                where.append("mem.birthday = '" + query.getBirthday()).append("' and ");
            }
            if (query.getRegistrationDate() != null) {
                where.append("mem.registration_date = '" + query.getRegistrationDate()).append("' and ");
            }
            if (StringUtils.isNotBlank(query.getMemberType())) {
                where.append("mem.member_type = '" + query.getMemberType() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getMemberTypeLike())) {
                where.append("mem.member_type like '%" + query.getMemberTypeLike() + "%'").append(" and ");
            }
            String memberTypeIn = StringUtils.toSqlInString(query.getMemberTypeIn());
            if (StringUtils.isNotBlank(memberTypeIn)) {
                where.append("mem.member_type in (" + memberTypeIn + ")").append(" and ");
            }
            String memberTypeNotIn = StringUtils.toSqlInString(query.getMemberTypeNotIn());
            if (StringUtils.isNotBlank(memberTypeNotIn)) {
                where.append("mem.member_type not in (" + memberTypeNotIn + ")").append(" and ");
            }
            if (query.getIntegral() != null) {
                where.append("mem.integral = " + query.getIntegral()).append(" and ");
            }
            if (StringUtils.isNotBlank(query.getAccountManager())) {
                where.append("mem.account_manager = '" + query.getAccountManager() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getAccountManagerLike())) {
                where.append("mem.account_manager like '%" + query.getAccountManagerLike() + "%'").append(" and ");
            }
            String accountManagerIn = StringUtils.toSqlInString(query.getAccountManagerIn());
            if (StringUtils.isNotBlank(accountManagerIn)) {
                where.append("mem.account_manager in (" + accountManagerIn + ")").append(" and ");
            }
            String accountManagerNotIn = StringUtils.toSqlInString(query.getAccountManagerNotIn());
            if (StringUtils.isNotBlank(accountManagerNotIn)) {
                where.append("mem.account_manager not in (" + accountManagerNotIn + ")").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getLabel())) {
                where.append("mem.label = '" + query.getLabel() + "'").append(" and ");
            }
            if (StringUtils.isNotBlank(query.getLabelLike())) {
                where.append("mem.label like '%" + query.getLabelLike() + "%'").append(" and ");
            }
            String labelIn = StringUtils.toSqlInString(query.getLabelIn());
            if (StringUtils.isNotBlank(labelIn)) {
                where.append("mem.label in (" + labelIn + ")").append(" and ");
            }
            String labelNotIn = StringUtils.toSqlInString(query.getLabelNotIn());
            if (StringUtils.isNotBlank(labelNotIn)) {
                where.append("mem.label not in (" + labelNotIn + ")").append(" and ");
            }
            if (query.getStart() != null) {
                where.append("mem.time >= '" + DateUtils.localDateTimeToStr(query.getStart()) + "'").append(" and ");
            }
            if (query.getEnd() != null) {
                where.append("mem.time <= '" + DateUtils.localDateTimeToStr(query.getEnd()) + "'").append(" and ");
            }
        }
        where.append("mem.del='0'");
        return where.toString();
    }
}

